package com.bw.mapper;

import com.bw.bean.Vo;
import com.bw.bean.Vo2;
import com.bw.bean.Voe;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface VoMapper {

    @Select("select concat(substr(birthday,3,1),'0后')name,count(*) value from user_info\n" +
            "group by concat(substr(birthday,3,1),'0后')")
    List<Vo> getList();

    @Select("select bt.name,\n" +
            "if(ui.gender='M','男','女') sex,\n" +
            "sum(oi.final_total_amount) value\n" +
            "from user_info ui\n" +
            "left join order_info oi on ui.id = oi.user_id\n" +
            "left join order_detail od on oi.id = od.order_id\n" +
            "left join sku_info si on od.sku_id = si.id\n" +
            "left join base_trademark bt on si.tm_id = bt.id\n" +
            "where bt.name is not null\n" +
            "group by bt.name,if(ui.gender='M','男','女');")
    List<Vo2> getList1();

    @Select("select '用户人' name,\n" +
            "    a.num1,b.num2 from\n" +
            "(select count(*) num1 from user_info where timestampdiff(year,create_time,now())<1) a,\n" +
            "(select count(*) num2 from user_info where timestampdiff(year,create_time,now())>=1) b")
    List<Voe> getList2();
}
